package com.mes.web.controller.biz;

import com.aspose.cells.PdfSaveOptions;
import com.aspose.cells.SaveFormat;
import com.github.pagehelper.PageInfo;
import com.mes.biz.domain.*;
import com.mes.biz.domain.vo.ExportStatement;
import com.mes.biz.domain.vo.ExportStatementDetail;
import com.mes.biz.service.*;
import com.mes.common.annotation.Log;
import com.mes.common.core.controller.BaseController;
import com.mes.common.core.domain.BaseEntity;
import com.mes.common.core.domain.R;
import com.mes.common.core.page.PageDomain;
import com.mes.common.enums.BusinessType;
import com.mes.common.utils.DateUtils;
import com.mes.common.utils.apose.LicenseUtil;
import com.mes.common.utils.poi.ExcelUtil;
import com.mes.common.utils.poi.ExportExecUtil;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.checkerframework.checker.units.qual.A;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.List;
import java.util.UUID;
import java.util.stream.Collectors;

/**
 * 对账单Controller
 *
 * @author wy
 * @email wy
 * @date 2023-05-19
 */
@RestController
@RequestMapping("/biz/bizcustomeraccount")
public class BizCustomerAccountController extends BaseController {

    @Autowired
    private BizCustomerAccountService bizCustomerAccountService;
    @Autowired
    private BizCustomerPayableDetailService bizCustomerPayableDetailService;
    @Autowired
    private BizPaymentService bizPaymentService;
    @Autowired
    private BizConfigService bizConfigService;

    /**
     * 对账列表
     */
    @GetMapping("/list")
    public R<PageInfo> list(BizCustomerAccount bizCustomerAccount, HttpServletRequest request, HttpServletResponse response) {
        bizCustomerAccount.setPage(new PageDomain(request, response));
        PageInfo<BizCustomerAccount> page = bizCustomerAccountService.findPage(bizCustomerAccount);
        return R.data(page);
    }

    /**
     * 获取对账明细
     */
    @GetMapping(value = "/detailAccount/{accountCode}/{type}")
    public R<BizCustomerAccount> detailBizCustomerAccount(@PathVariable("accountCode") String accountCode, @PathVariable("type") String type) {
        BizCustomerAccount bizCustomerAccount = bizCustomerAccountService.getBizCustomerAccountInfo(accountCode);
        if ("0".equals(type)) {
            type = null;
        }
        List<BizCustomerPayableDetail> bizCustomerPayableDetails = bizCustomerPayableDetailService.getBizCustomerPayableDetailByAccountCode(accountCode, type);
        bizCustomerAccount.setDetailList(bizCustomerPayableDetails);
        return R.data(bizCustomerAccount);
    }


    /**
     * 查询所有对账单列表
     */
    @GetMapping("/allList")
    public R<List<BizCustomerAccount>> list() {
        return R.data(bizCustomerAccountService.findList(null));
    }

    /**
     * 对账明细列表
     */
    @GetMapping(value = "/detail/{accountCode}")
    public R<List<BizCustomerPayableDetail>> detail(@PathVariable("accountCode") String accountCode) {
        List<BizCustomerPayableDetail> bizCustomerPayableDetails = bizCustomerPayableDetailService.getBizCustomerPayableDetailByAccountCode(accountCode, null);
        return R.data(bizCustomerPayableDetails);
    }

    /**
     * 新增对账单
     */
    @Log(title = "新增对账单", businessType = BusinessType.INSERT)
    @PostMapping
    public R add(@RequestBody @Validated BizCustomerPayable bizCustomerPayable) {
        return R.status(bizCustomerAccountService.createCustomerAccount(bizCustomerPayable));
    }

    @Log(title = "修改对账单", businessType = BusinessType.UPDATE)
    @PutMapping
    public R edit(@RequestBody @Validated BizCustomerPayable bizCustomerPayable) {
        return R.data(bizCustomerAccountService.updateCustomerAccount(bizCustomerPayable));
    }


    @PreAuthorize("@ss.hasPermi('biz:bizcustomeraccount:remove')")
    @Log(title = "对账单", businessType = BusinessType.DELETE)
    @Transactional(rollbackFor = Exception.class)
    @DeleteMapping("/{id}")
    public R remove(@PathVariable String id) {
        BizCustomerAccount bizCustomerAccount = bizCustomerAccountService.get(id);
        String accountCode = bizCustomerAccount.getAccountCode();
        List<BizPayment> bizPaymentList = bizPaymentService.getByAccountCode(accountCode);
        if (bizPaymentList != null && bizPaymentList.size() > 0) {
            return R.fail("对账单已有收款记录不能删除");
        }
        boolean res = bizCustomerAccountService.remove(bizCustomerAccount);
        if (res) {
            List<BizCustomerPayableDetail> list = bizCustomerPayableDetailService.getBizCustomerPayableDetailByAccountCode(accountCode, null);
            String[] ids = list.stream().map(BizCustomerPayableDetail::getId).toArray(String[]::new);
            if (ids.length > 0) {
                bizCustomerPayableDetailService.updateAccountCode(ids);
            }
        }
        return R.status(true);
    }

    @Log(title = "对账单导出", businessType = BusinessType.EXPORT)
    @GetMapping("/exportStatement")
    public R exportStatement(BizCustomerAccount bizCustomerAccount) {
        List<ExportStatement> statement = bizCustomerAccountService.exportStatement(bizCustomerAccount);
        ExcelUtil<ExportStatement> util = new ExcelUtil<ExportStatement>(ExportStatement.class);
//        return util.exportExcel(statement, "对账单");
        BizConfig configKey = bizConfigService.getByConfigKey(BaseEntity.STATEMENT_TITLE);

        return util.exportExcel(statement, "对账单", configKey.getRemark());
    }

    /**
     * 信息导出
     */
    @Log(title = "对账单导出", businessType = BusinessType.EXPORT)
    @GetMapping("export")
    public void export(HttpServletResponse response, BizCustomerAccount bizCustomerAccount) throws Exception {
        LicenseUtil.GetLicense();
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        OutputStream out=null;
        try {
            List<ExportStatement> list = bizCustomerAccountService.exportStatement(bizCustomerAccount);

            //创建sheet对象（excel的表单）
            HSSFSheet sheet = wb.createSheet("结算单");
            //设置列宽
            sheet.setColumnWidth(0, 5000);
            sheet.setColumnWidth(1, 6000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 4000);
            sheet.setColumnWidth(4, 5000);
            sheet.setColumnWidth(5, 4000);
            sheet.setColumnWidth(6, 4000);
            sheet.setColumnWidth(7, 4000);
            sheet.setColumnWidth(8, 4000);

            //标题字体
            HSSFFont titleFont = wb.createFont();
            titleFont.setFontName("宋体");
            titleFont.setFontHeightInPoints((short) 14);// 字体大小
            //标题样式
            HSSFCellStyle titleStyle = wb.createCellStyle();
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            titleStyle.setBorderTop(BorderStyle.THIN); // 上边框
            titleStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            titleStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            titleStyle.setBorderRight(BorderStyle.THIN); // 右边框
            titleStyle.setLocked(true);

            //标题标注字体
            HSSFFont titleFont2 = wb.createFont();
            titleFont2.setFontName("宋体");
            titleFont2.setFontHeightInPoints((short) 12);// 字体大小
            //标题标注样式
            HSSFCellStyle titleStyle2 = wb.createCellStyle();
            titleStyle2.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            titleStyle2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            titleStyle2.setBorderTop(BorderStyle.THIN); // 上边框
            titleStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
            titleStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
            titleStyle2.setBorderRight(BorderStyle.THIN); // 右边框
            titleStyle2.setFont(titleFont2);

            //表头字体
            HSSFFont headerFont = wb.createFont();
            headerFont.setFontName("宋体");
            headerFont.setFontHeightInPoints((short) 12);// 字体大小
            //表头样式
            HSSFCellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            headerStyle.setBorderTop(BorderStyle.THIN); // 上边框
            headerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            headerStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            headerStyle.setBorderRight(BorderStyle.THIN); // 右边框
            headerStyle.setFont(headerFont);

            //数据字体
            HSSFFont font = wb.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);// 字体大小
            //数据样式
            HSSFCellStyle style = wb.createCellStyle();
            style.setWrapText(true);// 自动换行
            style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            style.setBorderTop(BorderStyle.THIN); // 上边框
            style.setBorderBottom(BorderStyle.THIN); // 下边框
            style.setBorderLeft(BorderStyle.THIN); // 左边框
            style.setBorderRight(BorderStyle.THIN); // 右边框
            style.setFont(font);

            //数据字体
            HSSFFont font2 = wb.createFont();
            font2.setFontName("宋体");
            font2.setFontHeightInPoints((short) 14);// 字体大小
            //数据样式
            HSSFCellStyle style2 = wb.createCellStyle();
            style2.setWrapText(true);// 自动换行
            style2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            style2.setFont(font);

            //表头
            String[] columnNames = {"工程地址","区域","型号", "颜色", "数量", "单位", "单价", "金额", "备注"};
            String title = bizConfigService.getByConfigKey(BaseEntity.STATEMENT_TITLE).getRemark();
            String fileName = DateUtils.dateTime() + "_" + title+ ".xlsx";
            String remark = bizConfigService.getByConfigKey(BaseEntity.STATEMENT_REMARK).getRemark();
            int startRow = 0;
            if (!CollectionUtils.isEmpty(list)) {
                ExportStatement statement = list.get(0);
                //合并单元格
                CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, startRow, 0, 8);
                sheet.addMergedRegion(cellRangeAddress);

                //创建第一行
                HSSFRow row1 = sheet.createRow(startRow);
                //设置行高
                row1.setHeight((short) 800);
                //创建第一列
                HSSFCell cell = row1.createCell(0);
                cell.setCellValue(title);
                cell.setCellStyle(titleStyle);
                row1.setRowStyle(titleStyle);
                //发起单位及时间
                HSSFRow row2 = sheet.createRow(startRow + 1);
                row2.setHeight((short) 600);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("客户名称");
                cell20.setCellStyle(titleStyle2);

                //合并单元格
                CellRangeAddress cellAddresses1 = new CellRangeAddress(startRow + 1, startRow + 1, 1, 6);
                sheet.addMergedRegion(cellAddresses1);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue(statement.getCustomerName());
                cell21.setCellStyle(titleStyle2);
                HSSFCell cell8 = row2.createCell(2);
                cell8.setCellStyle(style);
                HSSFCell cell12 = row2.createCell(3);
                cell12.setCellStyle(style);
                HSSFCell cell10 = row2.createCell(4);
                cell10.setCellStyle(style);
                HSSFCell cell11 = row2.createCell(5);
                cell11.setCellStyle(style);
                HSSFCell cell14 = row2.createCell(6);
                cell14.setCellStyle(style);
                HSSFCell cell27 = row2.createCell(7);
                cell27.setCellValue("日期");
                cell27.setCellStyle(titleStyle2);
                HSSFCell cell28 = row2.createCell(8);
                cell28.setCellValue(statement.getReceiveDate());
                cell28.setCellStyle(titleStyle2);


                //表头
                HSSFRow row3 = sheet.createRow(startRow + 2);
                row3.setHeight((short) 600);
                for (int k = 0; k < columnNames.length; k++) {
                    HSSFCell cell3 = row3.createCell(k);
                    cell3.setCellValue(columnNames[k]);
                    cell3.setCellStyle(headerStyle);// 自动换行
                }

                int size = 0;
                //内容
                List<ExportStatementDetail> detailList = statement.getDetailList();
                BigDecimal countAmount = BigDecimal.ZERO;
                if (!CollectionUtils.isEmpty(detailList)) {
                    size = detailList.size();
                    for (int j = 0; j < detailList.size(); j++) {
                        ExportStatementDetail exportStatementDetail = detailList.get(j);

                        HSSFRow row = sheet.createRow(startRow + 2 + j + 1);
                        row.setHeight((short) 600);
                        HSSFCell cell13 = row.createCell(0);
                        cell13.setCellValue(exportStatementDetail.getAddress());
                        cell13.setCellStyle(style);// 自动换行

                        HSSFCell cell1 = row.createCell(1);
                        cell1.setCellValue(exportStatementDetail.getArea());
                        cell1.setCellStyle(style);// 自动换行

                        HSSFCell cell9 = row.createCell(2);
                        cell9.setCellValue(exportStatementDetail.getModel());
                        cell9.setCellStyle(style);// 自动换行

                        HSSFCell cell2 = row.createCell(3);
                        cell2.setCellValue(exportStatementDetail.getColor());
                        cell2.setCellStyle(style);// 自动换行

                        HSSFCell cell3 = row.createCell(4);
                        cell3.setCellValue(exportStatementDetail.getNumber().toString());
//                        cell3.setCellValue("123");
                        cell3.setCellStyle(style);

                        HSSFCell cell4 = row.createCell(5);
                        cell4.setCellValue(exportStatementDetail.getUnit());
                        cell4.setCellStyle(style);

                        HSSFCell cell5 = row.createCell(6);
                        cell5.setCellValue(exportStatementDetail.getPrice() != null ? exportStatementDetail.getPrice().toString(): "0");
                        cell5.setCellStyle(style);

                        HSSFCell cell6 = row.createCell(7);
                        cell6.setCellValue(exportStatementDetail.getAmount() !=null ? exportStatementDetail.getAmount().toString() :"0");
                        cell6.setCellStyle(style);

                        HSSFCell cell7 = row.createCell(8);
                        cell7.setCellValue(exportStatementDetail.getRemark());
                        cell7.setCellStyle(style);
                        countAmount = countAmount.add(exportStatementDetail.getAmount());
                    }

                }
                HSSFRow rowk2 = sheet.createRow(startRow + 2 + size + 1);
                rowk2.setHeight((short) 600);
                HSSFCell cell1 = rowk2.createCell(0);
                cell1.setCellValue("合计");
                cell1.setCellStyle(style);
                HSSFCell cell4 = rowk2.createCell(1);
                cell4.setCellStyle(style);
                HSSFCell cell5 = rowk2.createCell(2);
                cell5.setCellStyle(style);
                HSSFCell cell6 = rowk2.createCell(3);
                cell6.setCellStyle(style);
                HSSFCell cell7 = rowk2.createCell(4);
                cell7.setCellStyle(style);
                HSSFCell cell9 = rowk2.createCell(5);
                cell9.setCellStyle(style);
                HSSFCell cell15 = rowk2.createCell(6);
                cell15.setCellStyle(style);
                HSSFCell cell2 = rowk2.createCell(7);
                cell2.setCellValue(countAmount.toString());
                cell2.setCellStyle(style);
                HSSFCell cell13 = rowk2.createCell(8);
                cell13.setCellStyle(style);

                //合并单元格
                CellRangeAddress cellAddresses = new CellRangeAddress(startRow + 2 + size + 2, startRow + 2 + size + 2, 0, 8);
                sheet.addMergedRegion(cellAddresses);

                //创建最后一行
                HSSFRow row = sheet.createRow(startRow + 2 + size + 2);

                //设置行高
                row.setHeight((short) 2000);
                //创建第一列
                HSSFCell cell3 = row.createCell(0);
                cell3.setCellValue(remark);
                cell3.setCellStyle(style2);
            }


            //输出Excel文件
            out = response.getOutputStream();
            response.reset();
//            //设置响应头
//            response.setContentType("application/octet-stream");
//            fileName = new String(fileName.getBytes("gbk"),"ISO8859_1");
//            response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName +  "\"");
//            wb.write(out);
//            out.close();
            try {
                out = new FileOutputStream(ExcelUtil.getAbsoluteFile(fileName));
            } catch (Exception e) {
                e.printStackTrace();
            }
            wb.write(out);

            com.aspose.cells.Workbook wb2 = new com.aspose.cells.Workbook(ExcelUtil.getAbsoluteFile(fileName));
            PdfSaveOptions opts = new PdfSaveOptions();
            opts.setOnePagePerSheet(true);
            response.setContentType("application/pdf");
//            response.setHeader("Content-Disposition", "attachment; filename=“.xls");
            wb2.save(response.getOutputStream(), opts);

            IOUtils.closeQuietly(wb);
            IOUtils.closeQuietly(out);
//            return R.success(fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
//       return null;
    }

    @Log(title = "对账单导出", businessType = BusinessType.EXPORT)
    @GetMapping("exportExcel")
    public R exportExcel(HttpServletResponse response, BizCustomerAccount bizCustomerAccount) throws Exception {
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        OutputStream out=null;
        try {
            List<ExportStatement> list = bizCustomerAccountService.exportStatement(bizCustomerAccount);

            //创建sheet对象（excel的表单）
            HSSFSheet sheet = wb.createSheet("结算单");
            //设置列宽
            sheet.setColumnWidth(0, 5000);
            sheet.setColumnWidth(1, 6000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 4000);
            sheet.setColumnWidth(4, 5000);
            sheet.setColumnWidth(5, 4000);
            sheet.setColumnWidth(6, 4000);
            sheet.setColumnWidth(7, 4000);
            sheet.setColumnWidth(8, 4000);

            //标题字体
            HSSFFont titleFont = wb.createFont();
            titleFont.setFontName("宋体");
            titleFont.setFontHeightInPoints((short) 14);// 字体大小
            //标题样式
            HSSFCellStyle titleStyle = wb.createCellStyle();
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            titleStyle.setBorderTop(BorderStyle.THIN); // 上边框
            titleStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            titleStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            titleStyle.setBorderRight(BorderStyle.THIN); // 右边框
            titleStyle.setLocked(true);

            //标题标注字体
            HSSFFont titleFont2 = wb.createFont();
            titleFont2.setFontName("宋体");
            titleFont2.setFontHeightInPoints((short) 12);// 字体大小
            //标题标注样式
            HSSFCellStyle titleStyle2 = wb.createCellStyle();
            titleStyle2.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            titleStyle2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            titleStyle2.setBorderTop(BorderStyle.THIN); // 上边框
            titleStyle2.setBorderBottom(BorderStyle.THIN); // 下边框
            titleStyle2.setBorderLeft(BorderStyle.THIN); // 左边框
            titleStyle2.setBorderRight(BorderStyle.THIN); // 右边框
            titleStyle2.setFont(titleFont2);

            //表头字体
            HSSFFont headerFont = wb.createFont();
            headerFont.setFontName("宋体");
            headerFont.setFontHeightInPoints((short) 12);// 字体大小
            //表头样式
            HSSFCellStyle headerStyle = wb.createCellStyle();
            headerStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            headerStyle.setBorderTop(BorderStyle.THIN); // 上边框
            headerStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            headerStyle.setBorderLeft(BorderStyle.THIN); // 左边框
            headerStyle.setBorderRight(BorderStyle.THIN); // 右边框
            headerStyle.setFont(headerFont);

            //数据字体
            HSSFFont font = wb.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);// 字体大小
            //数据样式
            HSSFCellStyle style = wb.createCellStyle();
            style.setWrapText(true);// 自动换行
            style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            style.setBorderTop(BorderStyle.THIN); // 上边框
            style.setBorderBottom(BorderStyle.THIN); // 下边框
            style.setBorderLeft(BorderStyle.THIN); // 左边框
            style.setBorderRight(BorderStyle.THIN); // 右边框
            style.setFont(font);

            //数据字体
            HSSFFont font2 = wb.createFont();
            font2.setFontName("宋体");
            font2.setFontHeightInPoints((short) 14);// 字体大小
            //数据样式
            HSSFCellStyle style2 = wb.createCellStyle();
            style2.setWrapText(true);// 自动换行
            style2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            style2.setFont(font);

            //表头
            String[] columnNames = {"工程地址","区域","型号", "颜色", "数量", "单位", "单价", "金额", "备注"};
            String title = bizConfigService.getByConfigKey(BaseEntity.STATEMENT_TITLE).getRemark();
            String fileName = DateUtils.dateTime() + "_" + title+ ".xlsx";
            String remark = bizConfigService.getByConfigKey(BaseEntity.STATEMENT_REMARK).getRemark();
            int startRow = 0;
            if (!CollectionUtils.isEmpty(list)) {
                ExportStatement statement = list.get(0);
                //合并单元格
                CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, startRow, 0, 8);
                sheet.addMergedRegion(cellRangeAddress);

                //创建第一行
                HSSFRow row1 = sheet.createRow(startRow);
                //设置行高
                row1.setHeight((short) 800);
                //创建第一列
                HSSFCell cell = row1.createCell(0);
                cell.setCellValue(title);
                cell.setCellStyle(titleStyle);
                row1.setRowStyle(titleStyle);
                //发起单位及时间
                HSSFRow row2 = sheet.createRow(startRow + 1);
                row2.setHeight((short) 600);
                HSSFCell cell20 = row2.createCell(0);
                cell20.setCellValue("客户名称");
                cell20.setCellStyle(titleStyle2);

                //合并单元格
                CellRangeAddress cellAddresses1 = new CellRangeAddress(startRow + 1, startRow + 1, 1, 6);
                sheet.addMergedRegion(cellAddresses1);
                HSSFCell cell21 = row2.createCell(1);
                cell21.setCellValue(statement.getCustomerName());
                cell21.setCellStyle(titleStyle2);
                HSSFCell cell8 = row2.createCell(2);
                cell8.setCellStyle(style);
                HSSFCell cell12 = row2.createCell(3);
                cell12.setCellStyle(style);
                HSSFCell cell10 = row2.createCell(4);
                cell10.setCellStyle(style);
                HSSFCell cell11 = row2.createCell(5);
                cell11.setCellStyle(style);
                HSSFCell cell14 = row2.createCell(6);
                cell14.setCellStyle(style);
                HSSFCell cell27 = row2.createCell(7);
                cell27.setCellValue("日期");
                cell27.setCellStyle(titleStyle2);
                HSSFCell cell28 = row2.createCell(8);
                cell28.setCellValue(statement.getReceiveDate());
                cell28.setCellStyle(titleStyle2);


                //表头
                HSSFRow row3 = sheet.createRow(startRow + 2);
                row3.setHeight((short) 600);
                for (int k = 0; k < columnNames.length; k++) {
                    HSSFCell cell3 = row3.createCell(k);
                    cell3.setCellValue(columnNames[k]);
                    cell3.setCellStyle(headerStyle);// 自动换行
                }

                int size = 0;
                //内容
                List<ExportStatementDetail> detailList = statement.getDetailList();
                BigDecimal countAmount = BigDecimal.ZERO;
                if (!CollectionUtils.isEmpty(detailList)) {
                    size = detailList.size();
                    for (int j = 0; j < detailList.size(); j++) {
                        ExportStatementDetail exportStatementDetail = detailList.get(j);

                        HSSFRow row = sheet.createRow(startRow + 2 + j + 1);
                        row.setHeight((short) 600);
                        HSSFCell cell13 = row.createCell(0);
                        cell13.setCellValue(exportStatementDetail.getAddress());
                        cell13.setCellStyle(style);// 自动换行

                        HSSFCell cell1 = row.createCell(1);
                        cell1.setCellValue(exportStatementDetail.getArea());
                        cell1.setCellStyle(style);// 自动换行

                        HSSFCell cell9 = row.createCell(2);
                        cell9.setCellValue(exportStatementDetail.getModel());
                        cell9.setCellStyle(style);// 自动换行

                        HSSFCell cell2 = row.createCell(3);
                        cell2.setCellValue(exportStatementDetail.getColor());
                        cell2.setCellStyle(style);// 自动换行

                        HSSFCell cell3 = row.createCell(4);
                        cell3.setCellValue(exportStatementDetail.getNumber().toString());
//                        cell3.setCellValue("123");
                        cell3.setCellStyle(style);

                        HSSFCell cell4 = row.createCell(5);
                        cell4.setCellValue(exportStatementDetail.getUnit());
                        cell4.setCellStyle(style);

                        HSSFCell cell5 = row.createCell(6);
                        cell5.setCellValue(exportStatementDetail.getPrice() != null ? exportStatementDetail.getPrice().toString(): "0");
                        cell5.setCellStyle(style);

                        HSSFCell cell6 = row.createCell(7);
                        cell6.setCellValue(exportStatementDetail.getAmount() !=null ? exportStatementDetail.getAmount().toString() :"0");
                        cell6.setCellStyle(style);

                        HSSFCell cell7 = row.createCell(8);
                        cell7.setCellValue(exportStatementDetail.getRemark());
                        cell7.setCellStyle(style);
                        countAmount = countAmount.add(exportStatementDetail.getAmount());
                    }

                }
                HSSFRow rowk2 = sheet.createRow(startRow + 2 + size + 1);
                rowk2.setHeight((short) 600);
                HSSFCell cell1 = rowk2.createCell(0);
                cell1.setCellValue("合计");
                cell1.setCellStyle(style);
                HSSFCell cell4 = rowk2.createCell(1);
                cell4.setCellStyle(style);
                HSSFCell cell5 = rowk2.createCell(2);
                cell5.setCellStyle(style);
                HSSFCell cell6 = rowk2.createCell(3);
                cell6.setCellStyle(style);
                HSSFCell cell7 = rowk2.createCell(4);
                cell7.setCellStyle(style);
                HSSFCell cell9 = rowk2.createCell(5);
                cell9.setCellStyle(style);
                HSSFCell cell15 = rowk2.createCell(6);
                cell15.setCellStyle(style);
                HSSFCell cell2 = rowk2.createCell(7);
                cell2.setCellValue(countAmount.toString());
                cell2.setCellStyle(style);
                HSSFCell cell13 = rowk2.createCell(8);
                cell13.setCellStyle(style);

                //合并单元格
                CellRangeAddress cellAddresses = new CellRangeAddress(startRow + 2 + size + 2, startRow + 2 + size + 2, 0, 8);
                sheet.addMergedRegion(cellAddresses);

                //创建最后一行
                HSSFRow row = sheet.createRow(startRow + 2 + size + 2);

                //设置行高
                row.setHeight((short) 2000);
                //创建第一列
                HSSFCell cell3 = row.createCell(0);
                cell3.setCellValue(remark);
                cell3.setCellStyle(style2);
            }


            //输出Excel文件
            out = response.getOutputStream();
            response.reset();
//            //设置响应头
//            response.setContentType("application/octet-stream");
//            fileName = new String(fileName.getBytes("gbk"),"ISO8859_1");
//            response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + ".xls" + "\"");
//            wb.write(output);
//            output.close();
            try {
                out = new FileOutputStream(ExcelUtil.getAbsoluteFile(fileName));
            } catch (Exception e) {
                e.printStackTrace();
            }
            wb.write(out);
            IOUtils.closeQuietly(wb);
            IOUtils.closeQuietly(out);
            return R.success(fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 修改对账单类型
     * @param bizCustomerAccount
     * @return
     */
    @PostMapping(value = "/updateAccountType")
    public R updateAccountType(@RequestBody BizCustomerAccount bizCustomerAccount) {
        return R.data(bizCustomerAccountService.updateAccountType(bizCustomerAccount));
    }
}
